#include "goods.h"
#include "ui_goods.h"

#include <QMessageBox>
#include <QSqlError>

ReadOnlyDelegate* readOnlyDelegate = new ReadOnlyDelegate();

goods::goods(QWidget *parent) :
    QMainWindow(parent),
    ui(new Ui::goods)
{
    ui->setupUi(this);
    setWindowTitle("仓库货物管理");

    model1= new QSqlRelationalTableModel(this);
    model1->setTable
            ("goods");
    model1->select();
    model1 -> setEditStrategy(QSqlRelationalTableModel::OnManualSubmit);
    model1->setHeaderData(0,Qt::Horizontal,tr("商品号"));
    model1->setHeaderData(1,Qt::Horizontal,tr("类别号"));
    model1->setHeaderData(2,Qt::Horizontal,tr("商品名"));
    model1->setHeaderData(3,Qt::Horizontal,tr("价格"));
    model1->setHeaderData(4,Qt::Horizontal,tr("数量"));

    ui->tableView->setModel(model1);

    model2 = new QSqlRelationalTableModel(this);
    model2 ->setTable
            ("goods");
    model2 ->select();
    model2->setHeaderData(0,Qt::Horizontal,tr("商品号"));
    model2->setHeaderData(1,Qt::Horizontal,tr("类别号"));
    model2->setHeaderData(2,Qt::Horizontal,tr("商品名"));
    model2->setHeaderData(3,Qt::Horizontal,tr("价格"));
    model2->setHeaderData(4,Qt::Horizontal,tr("数量"));

    model3 = new QSqlRelationalTableModel(this);
    model3 ->setTable
            ("goods");
    model3 ->select();
    model3 -> setEditStrategy(QSqlRelationalTableModel::OnManualSubmit);
    model3->setHeaderData(0,Qt::Horizontal,tr("商品号"));
    model3->setHeaderData(1,Qt::Horizontal,tr("类别号"));
    model3->setHeaderData(2,Qt::Horizontal,tr("商品名"));
    model3->setHeaderData(3,Qt::Horizontal,tr("价格"));
    model3->setHeaderData(4,Qt::Horizontal,tr("数量"));

    model4= new QSqlRelationalTableModel(this);
    model4->setTable
            ("order1");
    model4->select();
    model4 -> setEditStrategy(QSqlRelationalTableModel::OnManualSubmit);
    model4->setHeaderData(0,Qt::Horizontal,tr("订单号"));
    model4->setHeaderData(1,Qt::Horizontal,tr("商品号"));
    model4->setHeaderData(2,Qt::Horizontal,tr("商品名"));
    model4->setHeaderData(3,Qt::Horizontal,tr("操作时间"));
    model4->setHeaderData(4,Qt::Horizontal,tr("数量"));
    model4->setHeaderData(5,Qt::Horizontal,tr("金额"));
    model4->setHeaderData(6,Qt::Horizontal,tr("状态"));

    ui->tableView->setModel(model1);

    ui->tableView->setColumnWidth(0, 100);
    ui->tableView->setColumnWidth(1, 100);
    ui->tableView->setColumnWidth(2, 350);
    ui->tableView->setColumnWidth(3, 80);
    ui->tableView->setColumnWidth(4, 80);
    ui->tableView->hideColumn(5);
    ui->tableView->setItemDelegateForColumn(0, readOnlyDelegate);
    ui->tableView->setItemDelegateForColumn(1, readOnlyDelegate);
//    ui->tableView->setItemDelegateForColumn(2, readOnlyDelegate);
    ui->tableView->setItemDelegateForColumn(3, readOnlyDelegate);
    ui->tableView->setItemDelegateForColumn(4, readOnlyDelegate);

    ui->tableView_2->setModel(model2);
    ui->tableView_2->setColumnWidth(0, 80);
    ui->tableView_2->setColumnWidth(1, 70);
    ui->tableView_2->setColumnWidth(2, 300);
    ui->tableView_2->setColumnWidth(3, 65);
    ui->tableView_2->setColumnWidth(4, 65);
    ui->tableView_2->verticalHeader()->setVisible(false);
    ui->tableView_2->hideColumn(5);
    ui->tableView_2->setSelectionBehavior(QAbstractItemView::SelectRows);
    ui->tableView_2->setEditTriggers(QAbstractItemView::NoEditTriggers);

    ui->tableView_3->setModel(model3);
    ui->tableView_3->setColumnWidth(0, 80);
    ui->tableView_3->setColumnWidth(1, 70);
    ui->tableView_3->setColumnWidth(2, 300);
    ui->tableView_3->setColumnWidth(3, 65);
    ui->tableView_3->setColumnWidth(4, 65);
    ui->tableView_3->verticalHeader()->setVisible(false);
    ui->tableView_3->hideColumn(5);
    ui->tableView_3->setSelectionBehavior(QAbstractItemView::SelectRows);
    ui->tableView_3->setEditTriggers(QAbstractItemView::NoEditTriggers);

    ui->tableView_4->setModel(model4);
    for(int r=0;r<ui->tableView_4->model()->rowCount();r++)
    {
        QString flag=model4->data(model4->index(r,6)).toString();
        if(flag=="0")
            model4->setData(model4->index(r,6),"入库");
        if(flag=="1")
            model4->setData(model4->index(r,6),"出库");
    }
    ui->tableView_4->setSelectionBehavior(QAbstractItemView::SelectRows);
    ui->tableView_4->setEditTriggers(QAbstractItemView::NoEditTriggers);
    ui->tableView_4->verticalHeader()->setVisible(false);
    ui->tableView_4->setColumnWidth(0, 80);
    ui->tableView_4->setColumnWidth(1, 80);
    ui->tableView_4->setColumnWidth(2, 350);
    ui->tableView_4->setColumnWidth(4, 80);
    ui->tableView_4->setColumnWidth(5, 80);
    ui->tableView_4->setColumnWidth(6, 80);
    ui->num_out->setMaximum(ui->stock->text().toInt());
    ui->num_in->setMaximum(INT_MAX);
    ui->id_out->setEnabled(false);
    ui->category_out->setEnabled(false);
    ui->in_price->setEnabled(false);
    ui->name_out->setEnabled(false);
    ui->stock->setEnabled(false);
    ui->comboBox_5->setVisible(false);
}

goods::~goods()
{
    delete ui;
}

void goods::refreshmodel1()
{
    model1->setTable("goods");
    model1->select();
    model1->setHeaderData(0,Qt::Horizontal,tr("商品号"));
    model1->setHeaderData(1,Qt::Horizontal,tr("类别号"));
    model1->setHeaderData(2,Qt::Horizontal,tr("商品名"));
    model1->setHeaderData(3,Qt::Horizontal,tr("价格"));
    model1->setHeaderData(4,Qt::Horizontal,tr("数量"));
    ui->tableView->setColumnWidth(0, 100);
    ui->tableView->setColumnWidth(1, 100);
    ui->tableView->setColumnWidth(2, 300);
    ui->tableView->setColumnWidth(3, 80);
    ui->tableView->setColumnWidth(4, 80);
    ui->tableView->hideColumn(5);
}

void goods::refreshmodel2()
{
    model2->setTable("goods");
    model2->select();
    model2->setHeaderData(0,Qt::Horizontal,tr("商品号"));
    model2->setHeaderData(1,Qt::Horizontal,tr("类别号"));
    model2->setHeaderData(2,Qt::Horizontal,tr("商品名"));
    model2->setHeaderData(3,Qt::Horizontal,tr("价格"));
    model2->setHeaderData(4,Qt::Horizontal,tr("数量"));
    ui->tableView_2->setColumnWidth(0, 80);
    ui->tableView_2->setColumnWidth(1, 70);
    ui->tableView_2->setColumnWidth(2, 300);
    ui->tableView_2->setColumnWidth(3, 65);
    ui->tableView_2->setColumnWidth(4, 65);
    ui->tableView_2->hideColumn(5);
}

void goods::refreshmodel3()
{
    model3->setTable("goods");
    model3->select();
    model3->setHeaderData(0,Qt::Horizontal,tr("商品号"));
    model3->setHeaderData(1,Qt::Horizontal,tr("类别号"));
    model3->setHeaderData(2,Qt::Horizontal,tr("商品名"));
    model3->setHeaderData(3,Qt::Horizontal,tr("价格"));
    model3->setHeaderData(4,Qt::Horizontal,tr("数量"));
    ui->tableView_3->setColumnWidth(0, 80);
    ui->tableView_3->setColumnWidth(1, 70);
    ui->tableView_3->setColumnWidth(2, 300);
    ui->tableView_3->setColumnWidth(3, 65);
    ui->tableView_3->setColumnWidth(4, 65);
    ui->tableView_3->hideColumn(5);
    ui->name_in->setEnabled(true);
    ui->Category_in->setEnabled(true);
    ui->inprice->setEnabled(true);
    ui->uploadPicture->setEnabled(true);
    ui->name_in->setText("");
    ui->inprice->setText("");
    ui->num_in->setValue(0);
    ui->prePictureLabel->clear();
}

void goods::refreshmodel4()
{
    model4->setTable
            ("order1");
    model4->select();
    model4 -> setEditStrategy(QSqlRelationalTableModel::OnManualSubmit);
    model4->setHeaderData(0,Qt::Horizontal,tr("订单号"));
    model4->setHeaderData(1,Qt::Horizontal,tr("商品号"));
    model4->setHeaderData(2,Qt::Horizontal,tr("商品名"));
    model4->setHeaderData(3,Qt::Horizontal,tr("操作时间"));
    model4->setHeaderData(4,Qt::Horizontal,tr("数量"));
    model4->setHeaderData(5,Qt::Horizontal,tr("金额"));
    model4->setHeaderData(6,Qt::Horizontal,tr("状态"));
    ui->tableView_4->setModel(model4);
    for(int r=0;r<ui->tableView_4->model()->rowCount();r++)
    {
        QString flag=model4->data(model4->index(r,6)).toString();
        if(flag=="0")
            model4->setData(model4->index(r,6),"入库");
        if(flag=="1")
            model4->setData(model4->index(r,6),"出库");
    }
    ui->tableView_4->setColumnWidth(0, 80);
    ui->tableView_4->setColumnWidth(1, 80);
    ui->tableView_4->setColumnWidth(2, 350);
    ui->tableView_4->setColumnWidth(4, 80);
    ui->tableView_4->setColumnWidth(5, 80);
    ui->tableView_4->setColumnWidth(6, 80);
}

void goods::on_SearchAll_clicked()
{
    refreshmodel1();
}

void goods::on_SearchAll_2_clicked()
{
    refreshmodel2();
}

void goods::on_SearchAll_3_clicked()
{
    refreshmodel3();
}

void goods::on_ok_clicked()
{
    model1->database().transaction();
       if(model1->submitAll())
       {
           if(model1->database().commit())
           {
               QMessageBox::information(this,"提交提醒","修改成功");
               ui->tableView_2->setModel(model2);//刷新数据网格
               refreshmodel2();
           }
       }
       else
       {
           model1->database().rollback();
           QMessageBox::information(this,"提交提醒",tr("提交错误").arg(model1->lastError().text()),QMessageBox::Yes);
       }
}

void goods::on_search_clicked()
{
    int i = ui->comboBox->currentIndex();
    QString str = ui->lineEdit->text();
    switch (i)
    {
    case 0:
        model1->setFilter(QString("id = '%1'").arg(str));
        model1->select();
        break;
    case 1:
        QString fliterstr=QObject::tr("name like '%%1%'").arg(str);
        model1->setFilter(fliterstr);
        model1->select();
        break;
    }
}

void goods::on_search_2_clicked()
{
    int i = ui->comboBox_2->currentIndex();
    QString str = ui->lineEdit->text();
    switch (i) {
    case 0:
        model2->setFilter(QString("id = '%1'").arg(str));
        model2->select();
        break;
    case 1:
        QString fliterstr=QObject::tr("name like '%%1%'").arg(str);
        model2->setFilter(fliterstr);
        model2->select();
        break;
    }
}

void goods::on_search_3_clicked()
{
    int i = ui->comboBox_3->currentIndex();
    QString str = ui->lineEdit_3->text();
    switch (i) {
    case 0:
        model3->setFilter(QString("id = '%1'").arg(str));
        model3->select();
        break;
    case 1:
        QString fliterstr=QObject::tr("name like '%%1%'").arg(str);
        model3->setFilter(fliterstr);
        model3->select();
        break;
    }
}

void goods::on_revoke_clicked()
{
    model1->revertAll();
}

void goods::on_refresh_clicked()
{
    refreshmodel2();
}

void goods::on_uploadPicture_clicked()
{
    QString picturename = QFileDialog::getOpenFileName(this, "选择商品图片", ".", "Image File(*.png *.jpg *.jpeg *.bmp)");
    if (picturename.isEmpty()) return;
    myPicImg.load(picturename);
    ui->prePictureLabel->setPixmap(QPixmap::fromImage(myPicImg));
}

void goods::onTable2SelectChange(int row)
{
    int r = 1;
    if(row != 0) r = ui->tableView_3->currentIndex().row();

    QModelIndex index;
    index = model3->index(r, 2);                   //名称
    ui->name_in->setText(model3->data(index).toString());
    index = model3->index(r, 3);                   //进价
    ui->inprice->setText(model3->data(index).toString());

    QPixmap photo;
    QSqlQueryModel *pictureModel = new QSqlQueryModel(this);
    QString name = ui->name_in->toPlainText();
    pictureModel->setQuery("select picture from goods where name='" + name + "'");
    index = pictureModel->index(0, 0);
    photo.loadFromData(pictureModel->data(index).toByteArray(), "JPG");
    ui->prePictureLabel->setPixmap(photo);

    QSqlQuery query;
    query.exec(QString("select name from category where categoryid=(select categoryid from goods where name='%1')").arg(ui->name_in->toPlainText()));
    query.next();
    ui->Category_in->setCurrentText(query.value(0).toString());//获取category
    ui->name_in->setEnabled(false);
    ui->Category_in->setEnabled(false);
    ui->uploadPicture->setEnabled(false);
}

void goods::on_instock_clicked()
{
    refreshmodel1();
    int r = ui->tableView->model()->rowCount();
    int flag=0,t;
    bool m;
    for (int row = 0; row < r; row++)
    {
        QString qa=ui->name_in->toPlainText();
        QString qb=model1->data(model1->index(row,2)).toString();
        string a=qa.toStdString();
        string b=qb.toStdString();
        if(a==b)
        {
            flag++;
        }

    }
    if(!flag)//新商品入库
    {
        QSqlQuery query;
        query.exec(QString("select categoryid from category where name='%1'").arg(ui->Category_in->currentText()));
        query.next();
        int categoryid = query.value(0).toInt();//将要入库的商品类别
        QString Name = ui->name_in->toPlainText();//商品名称
        double inputprice = ui->inprice->text().toDouble();//进价
        int count = ui->num_in->value();//入库量
        m=query.exec(QString("insert into goods(categoryid, name, picture, price, num) values(%1, '%2', NULL, %3, %4)")
                     .arg(categoryid).arg(Name).arg(inputprice).arg(count));//插入新入库的商品记录
        query.exec(QString("select id from goods where name='%1'").arg(Name));
        query.next();
        int id=query.value(0).toInt();
       //插入照片
        QByteArray picdata;
        QBuffer buffer(&picdata);
        buffer.open(QIODevice::WriteOnly);
        myPicImg.save(&buffer, "JPG");
        QVariant var(picdata);
        QString sqlstr = "update goods set picture=? where name='" + Name + "'";
        query.prepare(sqlstr);
        query.addBindValue(var);
        query.exec();
        query.next();
        t=query.exec(QString("insert into order1(time,name,count,price,flag,id) values('%1','%2',%3,%4,%5,%6)")
                     .arg(QDateTime::currentDateTime().toString("hh:mm yy/M/d")).arg(Name).arg(count).arg(inputprice).arg(0).arg(id));
        refreshmodel4();
    }
    else//已有商品入库
    {
        QSqlQuery query;
        query.exec(QString("select id from goods where name='%1'").arg(ui->name_in->toPlainText()));
        query.next();
        int id = query.value(0).toInt();
        int num_in = ui->num_in->value();
        r = ui->tableView_3->currentIndex().row();
        double preinprice=model3->data(model3->index(r,3)).toDouble();
        int prenum=model3->data(model3->index(r,4)).toInt();
        double inprice=ui->inprice->text().toDouble();
        int num = prenum + num_in;
        double price=(preinprice*prenum+inprice*num_in)/num;
        query.exec(QString("select name from goods where id='%1'").arg(id));
        query.next();
        QString name=query.value(0).toString();
        if(num_in==0)
        {
            QMessageBox::warning(0, QObject::tr("提示"), "入库数量不能为0！");
        }
        else
        {
            m=query.exec(QString("update goods set num=%1,price=%2 where id=%3").arg(num).arg(price).arg(id));//更新库存
            query.next();
            QSqlDatabase::database().transaction();//开始事务
            if (m)
            {
                QSqlDatabase::database().commit();
                t=query.exec(QString("insert into order1(id,time,count,price,flag,name) values(%1,'%2',%3,%4,%5,'%6')")
                             .arg(id).arg(QDateTime::currentDateTime().toString("hh:mm yy/M/d")).arg(num_in).arg(inprice).arg(0).arg(name));
                refreshmodel4();
            }
//        t=query.exec();
        }
    }
    if(m&&!t)
    {
        QMessageBox::information(0, QObject::tr("提示"), "入库成功，但写入订单失败");
    }
    else if(!m)
    {
        QMessageBox::information(0,QObject::tr("提示"),"入库失败，请检查数据库连接!");
    }
    else
    {
        QMessageBox::information(0, QObject::tr("提示"), "入库成功");
        ui->name_in->setEnabled(true);
        ui->Category_in->setEnabled(true);
        ui->inprice->setEnabled(true);
        ui->uploadPicture->setEnabled(true);
        ui->name_in->setText("");
        ui->inprice->setText("");
        ui->num_in->setValue(0);
        ui->prePictureLabel->clear();
    }
    refreshmodel1();
    refreshmodel2();
    refreshmodel3();
}
void goods::showPhoto()
{
    QPixmap photo;
    QModelIndex index;
    QSqlQueryModel *pictureModel = new QSqlQueryModel(this);//商品样照模型数据
    QString name = ui->name_out->toPlainText();
    pictureModel->setQuery("select picture from goods where name='" + name + "'");
    index = pictureModel->index(0, 0);
    photo.loadFromData(pictureModel->data(index).toByteArray(), "JPG");
    ui->PictureLabel_2->setPixmap(photo);
}

void goods::onTableSelectChange(int row)
{
    int r = 1;
    if(row != 0) r = ui->tableView_2->currentIndex().row();

    QModelIndex index;
    index = model2->index(r, 0);                   //id
    ui->id_out->setText(model2->data(index).toString());
    index = model2->index(r, 2);                   //名称
    ui->name_out->setText(model2->data(index).toString());
    index = model2->index(r, 3);                   //进价
    ui->in_price->setText(model2->data(index).toString());
    index = model2->index(r, 4);                   //库存
    ui->stock->setText(model2->data(index).toString());
    showPhoto();                                   //商品样照
    ui->num_out->setMaximum(ui->stock->text().toInt());

    QSqlQuery query;
    query.exec(QString("select name from category where categoryid=(select categoryid from goods where Name='%1')").arg(ui->name_out->toPlainText()));
    query.next();
    ui->category_out->setText(query.value(0).toString());//category
}

void goods::on_tableView_2_clicked(const QModelIndex &index)
{
    onTableSelectChange(1);
}

void goods::oninformationChange()
{
    int r;
    r = ui->tableView_2->currentIndex().row();
    QModelIndex index;
    index = model2->index(r, 2);                   //进价
    ui->in_price->setText(model2->data(index).toString());
    ui->total_price->setText(QString::number(ui->lineEdit_5->text().toDouble() * ui->num_out->value()));
    ui->profit->setText(QString::number((ui->lineEdit_5->text().toDouble()-ui->in_price->text().toDouble()) * ui->num_out->value()));
}

void goods::on_name_out_currentIndexChanged(int index)
{
    oninformationChange();
}

void goods::on_outstock_clicked()
{
    QSqlQuery query;
    query.exec(QString("select id from goods where name='%1'").arg(ui->name_out->toPlainText()));
    query.next();
    int id = query.value(0).toInt();
    int num_out = ui->num_out->value();
    int num = ui->stock->text().toInt() - num_out;//剩余库存量
    double price=ui->lineEdit_5->text().toInt();
    if(price<=0||num_out==0)
    {
            QMessageBox::warning(0, QObject::tr("提示"), "出库失败，请检查是否选中商品，出库价格和数量是否正确");

    }
    else
    {
        bool t=query.exec(QString("update goods set num=%1 where id=%2").arg(num).arg(id));//更新库存
        QSqlDatabase::database().transaction();//开始事务
        if (t)
        {
            if(QSqlDatabase::database().commit())
            {
                oninformationChange();
                query.exec(QString("insert into order1(time,id,count,price,flag,name) values('%1',%2,%3,%4,%5,'%6')")
                                     .arg(QDateTime::currentDateTime().toString("hh:mm yy/M/d")).arg(id).arg(num_out).arg(price).arg(1).arg(ui->name_out->toPlainText()));
                refreshmodel4();
                QMessageBox::information(0, QObject::tr("提示"), "出库成功！");
                refreshmodel2();
                refreshmodel1();
                ui->name_out->setText("");
                ui->category_out->setText("");
                ui->in_price->setText("");
                ui->stock->setText("");
                ui->lineEdit_5->setText("");
                ui->id_out->setText("");
                ui->num_out->setValue(0);
                ui->PictureLabel_2->clear();
            }
        }
        else
        {
            QSqlDatabase::database().rollback();
        }
    }
}

void goods::on_id_out_textChanged(const QString &arg1)
{
    oninformationChange();
}

void goods::on_lineEdit_5_editingFinished()
{
    oninformationChange();
}

void goods::on_num_out_editingFinished()
{
    oninformationChange();
}

void goods::on_pushButton_clicked()
{
    refreshmodel1();
    QXlsx::Document xlsx;
    QXlsx::Format formatTitle;
    formatTitle.setFontBold(true);
    formatTitle.setFontColor(QColor(Qt::black));
    formatTitle.setFontSize(20);
    formatTitle.setBorderStyle(QXlsx::Format::BorderThin);
    formatTitle.setPatternBackgroundColor(Qt::lightGray);
    formatTitle.setFillPattern(QXlsx::Format::PatternSolid);
    formatTitle.setHorizontalAlignment(QXlsx::Format::AlignHCenter);
    xlsx.mergeCells(QXlsx::CellRange(1,1, 1, 5), formatTitle);
    xlsx.write(1, 1, "在库商品详情表");

    xlsx.setColumnWidth(3,3,50);

    QXlsx::Format format1;
    format1.setFontBold(false);
    format1.setFontSize(14);
    format1.setFontColor(QColor(Qt::black));
    format1.setBorderStyle(QXlsx::Format::BorderThin);
    format1.setPatternBackgroundColor(Qt::darkGray);


    xlsx.write(2, 1, "商品号", format1);
    xlsx.write(2, 2, "类别号", format1);
    xlsx.write(2, 3, "商品名", format1);
    xlsx.write(2, 4, "入库价", format1);
    xlsx.write(2, 5, "数量", format1);


    QXlsx::Format format2;
    format2.setFontBold(false);
    format2.setFontColor(QColor(Qt::black));
    format2.setBorderStyle(QXlsx::Format::BorderThin);
    format2.setFont(QStringLiteral("SimSun"));
    int r = ui->tableView->model()->rowCount();
    QXlsx::Format format3;
    format3.setFontBold(false);
    format3.setFontColor(QColor(Qt::black));
    format3.setFont(QStringLiteral("SimSun"));
    xlsx.write(1, 6, QDateTime::currentDateTime(),format3);
    xlsx.setColumnWidth(6,6,15);

    for (int col = 1; col <= 5; col++)
    {
      for (int row = 3; row <= r+2; row++)
      {
        xlsx.write(row, col, model1->data(model1->index(row-3,col-1)), format2);
      }
    }

    xlsx.saveAs("在库商品详情表.xlsx");
    QDesktopServices::openUrl(QUrl::fromLocalFile(QFileInfo("在库商品详情表.xlsx").absoluteFilePath()));
}

void goods::on_refresh_2_clicked()
{
    refreshmodel3();
    ui->name_in->setEnabled(true);
    ui->Category_in->setEnabled(true);
    ui->inprice->setEnabled(true);
}

void goods::on_tableView_3_clicked(const QModelIndex &index)
{
    onTable2SelectChange(1);
}

void goods::on_pushButton_2_clicked()
{
    ui->name_in->setEnabled(true);
    ui->Category_in->setEnabled(true);
    ui->inprice->setEnabled(true);
    ui->uploadPicture->setEnabled(true);
}

void goods::on_ordertoexcel_clicked()
{
    refreshmodel4();
    QXlsx::Document xlsx;
    QXlsx::Format formatTitle;
    formatTitle.setFontBold(true);
    formatTitle.setFontColor(QColor(Qt::black));
    formatTitle.setFontSize(20);
    formatTitle.setBorderStyle(QXlsx::Format::BorderThin);
    formatTitle.setPatternBackgroundColor(Qt::lightGray);
    formatTitle.setFillPattern(QXlsx::Format::PatternSolid);
    formatTitle.setHorizontalAlignment(QXlsx::Format::AlignHCenter);
    xlsx.mergeCells(QXlsx::CellRange(1,1, 1, 7), formatTitle);
    xlsx.write(1, 1, "仓库订单详情表");

    xlsx.setColumnWidth(3,50);
    xlsx.setColumnWidth(4,18);

    QXlsx::Format format1;
    format1.setFontBold(false);
    format1.setFontSize(14);
    format1.setFontColor(QColor(Qt::black));
    format1.setBorderStyle(QXlsx::Format::BorderThin);
    format1.setPatternBackgroundColor(Qt::darkGray);
    format1.setVerticalAlignment(QXlsx::Format::AlignVCenter);
    format1.setHorizontalAlignment(QXlsx::Format::AlignHCenter);

    xlsx.write(2, 1, "订单号", format1);
    xlsx.write(2, 2, "商品号", format1);
    xlsx.write(2, 3, "商品名", format1);
    xlsx.write(2, 4, "操作时间", format1);
    xlsx.write(2, 5, "数量", format1);
    xlsx.write(2, 6, "金额", format1);
    xlsx.write(2, 7, "状态", format1);


    QXlsx::Format format2;
    format2.setVerticalAlignment(QXlsx::Format::AlignVCenter);
    format2.setHorizontalAlignment(QXlsx::Format::AlignHCenter);
    format2.setFontBold(false);
    format2.setFontColor(QColor(Qt::black));
    format2.setBorderStyle(QXlsx::Format::BorderThin);
    format2.setFont(QStringLiteral("SimSun"));
    int r = ui->tableView_4->model()->rowCount();
    QXlsx::Format format3;
    format3.setFontBold(false);
    format3.setFontColor(QColor(Qt::black));
    format3.setFont(QStringLiteral("SimSun"));
    xlsx.write(1, 8, QDateTime::currentDateTime(),format3);
    xlsx.setColumnWidth(8,15);

    for (int col = 1; col <= 7; col++)
    {
      for (int row = 3; row <= r+2; row++)
      {
        xlsx.write(row, col, model4->data(model4->index(row-3,col-1)), format2);
      }
    }


    xlsx.saveAs("仓库订单详情表.xlsx");
    QDesktopServices::openUrl(QUrl::fromLocalFile(QFileInfo("仓库订单详情表.xlsx").absoluteFilePath()));
}

void goods::on_searchall_clicked()
{
    refreshmodel4();
}

void goods::on_search_4_clicked()
{
    int i = ui->comboBox_4->currentIndex(),j;
    QString str = ui->lineEdit_4->text();
    QString fliterstr;
    switch (i) {
    case 0:
        model4->setFilter(QString("OrderID = '%1'").arg(str));
        model4->select();
        break;
    case 1:
        model4->setFilter(QString("id = '%1'").arg(str));
        model4->select();
        break;
    case 2:
        fliterstr=QObject::tr("name like '%%1%'").arg(str);
        model4->setFilter(fliterstr);
        model4->select();
        break;
    case 3:
        fliterstr=QObject::tr("time like '%%1%'").arg(str);
        model4->setFilter(fliterstr);
        model4->select();
        break;
    case 4:
        j=ui->comboBox_5->currentIndex();
        switch (j) {
        case 0:
            model4->setFilter(QString("flag = '%1'").arg("0"));
            model4->select();
            break;
        case 1:
            model4->setFilter(QString("flag = '%1'").arg("1"));
            model4->select();
            break;
        }
    }
    for(int r=0;r<ui->tableView_4->model()->rowCount();r++)
    {
        QString flag=model4->data(model4->index(r,6)).toString();
        if(flag=="0")
            model4->setData(model4->index(r,6),"入库");
        if(flag=="1")
            model4->setData(model4->index(r,6),"出库");
    }
}

void goods::on_comboBox_4_currentIndexChanged(int index)
{
    if (index==4)
    {
        ui->lineEdit_4->setVisible(false);
        ui->comboBox_5->setVisible(true);
    }
    else
    {
        ui->lineEdit_4->setVisible(true);
        ui->comboBox_5->setVisible(false);
    }
}

